﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Security.Policy;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
namespace XW.Utils
{
    /// <summary>
    /// Excel 导出
    /// </summary>
    public static class NPOIHelper
    {
        public static byte[] ExportXLSX(DataTable data, Dictionary<string, string> columns, string name)
        {
            List<ExcelColumn> list = new List<ExcelColumn>();
            foreach (string key in columns.Keys)
            {
                list.Add(new ExcelColumn { field = key, header = columns[key] });
            }
            return ExportXLSX(data, list, name);
        }
        public static byte[] ExportXLSX(List<dynamic> data, Dictionary<string, string> columns, string name)
        {
            List<ExcelColumn> list = new List<ExcelColumn>();
            foreach (string key in columns.Keys)
            {
                list.Add(new ExcelColumn { field = key, header = columns[key] });
            }
            return ExportXLSX(data, list, name);
        }
        public static byte[] ExportXLSX(List<dynamic> data, List<ExcelColumn> columns, string name)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(name);
            int HeaderRowsMax = 1;
            if (columns.Where(c => c.columns != null && c.columns.Count > 0).Count() > 0)
            {
                HeaderRowsMax = 2;
            }
            IRow headerRow1 = sheet.CreateRow(0);
            int column = 0;
            var headFont = workbook.CreateFont();
            headFont.IsBold = true; //字体为粗体
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.SetFont(headFont);
            IRow headerRow2 = null;
            if (HeaderRowsMax == 2) headerRow2 = sheet.CreateRow(1);
            foreach (var col in columns)
            {
                var cel = headerRow1.CreateCell(column);
                cel.SetCellValue(col.header);
                cel.SetCellType(CellType.String);
                cel.CellStyle = headStyle;
                if (col.columns != null && col.columns.Count > 0)
                {
                    if (col.columns.Count > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, column, column + col.columns.Count));
                    }
                    foreach (var col2 in col.columns)
                    {
                        var cel2 = headerRow2.CreateCell(column);
                        cel2.SetCellValue(col2.header);
                        cel.SetCellType(CellType.String);
                        cel.CellStyle = headStyle;
                        column++;
                    }
                }
                else
                {
                    if (HeaderRowsMax > 2)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 1, column, column)); // 合并单元格 
                    }
                    column++;
                }
            }
            if (data.Any())
            {
                for (int i = 0; i < data.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + HeaderRowsMax); // 创建数据行
                    column = 0;
                    var dic = (data[i] as IDictionary<string, object>);
                    foreach (var col in columns)
                    {

                        if (col.columns != null && col.columns.Count > 0)
                        {
                            foreach (var col2 in col.columns)
                            {
                                ICell cel = dataRow.CreateCell(column);
                                object value;
                                if (!dic.TryGetValue(col2.field, out value))
                                    value = "";
                                if (value != null)
                                {
                                    string tmp = value.ToString();
                                    value = tmp.Replace("<br>", "\r\n");
                                }
                                else
                                    value = "";
                                cel.SetCellValue(value.ToString());
                                column++;
                            }
                        }
                        else
                        {
                            ICell cel = dataRow.CreateCell(column);
                            object value;
                            if (!dic.TryGetValue(col.field, out value))
                                value = "";
                            if (value != null)
                            {
                                string tmp = value.ToString();
                                value = tmp.Replace("<br>", "\r\n");
                            }
                            else
                                value = "";
                            cel.SetCellValue(value.ToString());
                            column++;
                        }
                    }
                }
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();

            return ms.ToArray();
        }

        public static byte[] ExportExcel(IList data, Dictionary<string, string> columns, string name)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(name);
            int HeaderRowsMax = 1;
            
            IRow headerRow1 = sheet.CreateRow(0);
            int column = 0;
            var headFont = workbook.CreateFont();
            headFont.IsBold = true; //字体为粗体
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.SetFont(headFont); 
            foreach (var col in columns)
            {
                var cel = headerRow1.CreateCell(column);
                cel.SetCellValue(col.Value);
                cel.SetCellType(CellType.String);
                cel.CellStyle = headStyle;
                column++;
            }
            if (data.Count > 0)
            {
                for (int i = 0; i < data.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + HeaderRowsMax); // 创建数据行
                    column = 0;
                    var dic = (data[i]).GetPropertiesDic();
                    foreach (var col in columns)
                    {

                        ICell cel = dataRow.CreateCell(column);
                        object value;
                        if (!dic.TryGetValue(col.Key, out value))
                            value = "";
                        if (value is decimal)
                        {
                            cel.SetCellValue((double)CommonHelper.ToDecimal(value));
                        }
                        else if (value is int)
                        {
                            cel.SetCellValue((double)CommonHelper.ToInt(value));
                        }
                        
                        else {

                           
                            if (value != null)
                            {
                                string tmp = value.ToString();
                                value = tmp.Replace("<br>", "\r\n");
                            }
                            else
                                value = "";
                            cel.SetCellValue(value.ToString());
                        }
                        column++;
                    }
                }
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();

            return ms.ToArray();
        }

        public static byte[] ExportXLSX(DataTable dt, List<ExcelColumn> columns, string name)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(name);
            int HeaderRowsMax = 1;
            if (columns.Where(c => c.columns != null && c.columns.Count > 0).Count() > 0)
            {
                HeaderRowsMax = 2;
            }
            IRow headerRow1 = sheet.CreateRow(0);
           
            var headFont = workbook.CreateFont();
            headFont.FontName = "宋体";
            headFont.FontHeightInPoints = 12;
            headFont.IsBold = true; //字体为粗体
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            IDataFormat format = workbook.CreateDataFormat();
            headStyle.SetFont(headFont);
            IRow headerRow2 = null;
            if (HeaderRowsMax == 2) headerRow2 = sheet.CreateRow(1);
            int column = 0;
            foreach (var col in columns)
            {

                SetColCellType(col, dt);
                var cel = headerRow1.CreateCell(column);
                cel.SetCellValue(col.header);
                cel.SetCellType(CellType.String);
                cel.CellStyle = headStyle;
                if (col.columns != null && col.columns.Count > 0)
                {
                    if (col.columns.Count > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, column, column + col.columns.Count));
                    }
                    foreach (var col2 in col.columns)
                    {
                        SetColCellType(col2, dt);
                        var cel2 = headerRow2.CreateCell(column);
                        cel2.SetCellValue(col2.header);
                        cel.SetCellType(CellType.String);
                        cel.CellStyle = headStyle;
                        column++;
                    }
                }
                else
                {
                    if (HeaderRowsMax > 2)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 1, column, column)); // 合并单元格 
                    }
                    column++;
                }
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + HeaderRowsMax); // 创建数据行
                    var dic = dt.Rows[i];
                    column = 0;
                    foreach (var col in columns)
                    {

                        if (col.columns != null && col.columns.Count > 0)
                        {
                            foreach (var col2 in col.columns)
                            {
                                ICell cel = dataRow.CreateCell(column);
                                string value = dic[col2.field].ToString();

                                SetCellValue(cel, col2, value, workbook, format);
                                column++;
                            }
                        }
                        else
                        {
                            ICell cel = dataRow.CreateCell(column);
                            string value = dic[col.field].ToString();
                            SetCellValue(cel, col, value, workbook, format);
                            column++;
                        }
                    }
                }
            }

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            workbook = null;
            return ms.ToArray();
        }

        public static void ExportXLSX(DataTable dt, List<ExcelColumn> columns, string dir,string name)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(name);
            int HeaderRowsMax = 1;
            if (columns.Where(c => c.columns != null && c.columns.Count > 0).Count() > 0)
            {
                HeaderRowsMax = 2;
            }
            IRow headerRow1 = sheet.CreateRow(0);

            var headFont = workbook.CreateFont();
            headFont.FontName = "宋体";
            headFont.FontHeightInPoints = 12;
            headFont.IsBold = true; //字体为粗体
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            IDataFormat format = workbook.CreateDataFormat();
            headStyle.SetFont(headFont);
            headStyle.WrapText = true;//自动换行
            IRow headerRow2 = null;
            if (HeaderRowsMax == 2) headerRow2 = sheet.CreateRow(1);
            int column = 0;
            foreach (var col in columns)
            {
                if (col.colWidth > 0)
                {
                    //设置列宽
                    sheet.SetColumnWidth(column, col.colWidth * 256);
                }
                SetColCellType(col, dt);
                var cel = headerRow1.CreateCell(column);
                cel.SetCellValue(col.header);
                cel.SetCellType(CellType.String);
                cel.CellStyle = headStyle;
                if (col.columns != null && col.columns.Count > 0)
                {
                    if (col.columns.Count > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, column, column + col.columns.Count - 1));//需-1，防止多合并一列
                    }
                    foreach (var col2 in col.columns)
                    {
                        SetColCellType(col2, dt);
                        var cel2 = headerRow2.CreateCell(column);
                        cel2.SetCellValue(col2.header);
                        cel.SetCellType(CellType.String);
                        cel.CellStyle = headStyle;
                        //第二行表头也可以设置列宽
                        if (col2.colWidth > 0)
                        {
                            //设置列宽
                            sheet.SetColumnWidth(column, col2.colWidth * 256);
                        }
                        column++;
                    }
                }
                else
                {
                    if (HeaderRowsMax == 2)//大于2改为==2，HeaderRowsMax只有1和2，防止永远不合并单元格
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 1, column, column)); // 合并单元格 
                    }
                    column++;
                }
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + HeaderRowsMax); // 创建数据行
                    var dic = dt.Rows[i];
                    column = 0;
                    foreach (var col in columns)
                    {

                        if (col.columns != null && col.columns.Count > 0)
                        {
                            foreach (var col2 in col.columns)
                            {
                                ICell cel = dataRow.CreateCell(column);
                                string value = dic[col2.field].ToString();

                                SetCellValue(cel, col2, value, workbook, format);
                                column++;
                            }
                        }
                        else
                        {
                            ICell cel = dataRow.CreateCell(column);
                            string value = dic[col.field].ToString();
                            SetCellValue(cel, col, value, workbook, format);
                            column++;
                        }
                    }
                }
            }
            string filePath = dir + name + ".xlsx";
            string directoryName = Path.GetDirectoryName(filePath);
            if ((directoryName.Length > 0) && (!Directory.Exists(directoryName)))
            {
                Directory.CreateDirectory(directoryName);
            }
            FileStream fileS = new FileStream(filePath, FileMode.Create);//保存 

            workbook.Write(fileS);
            fileS.Close(); 
            workbook = null; 
        }

        public static void ExportImgXLSX(DataTable dt, List<ExcelColumn> columns, string dir, string name)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(name);
            int HeaderRowsMax = 1;
            if (columns.Where(c => c.columns != null && c.columns.Count > 0).Count() > 0)
            {
                HeaderRowsMax = 2;
            }
            IRow headerRow1 = sheet.CreateRow(0);

            var headFont = workbook.CreateFont();
            headFont.FontName = "宋体";
            headFont.FontHeightInPoints = 12;
            headFont.IsBold = true; //字体为粗体
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            IDataFormat format = workbook.CreateDataFormat();
            headStyle.SetFont(headFont);
            headStyle.WrapText = true;//自动换行
            IRow headerRow2 = null;
            if (HeaderRowsMax == 2) headerRow2 = sheet.CreateRow(1);
            int column = 0;
            foreach (var col in columns)
            {
                if (col.colWidth > 0)
                {
                    //设置列宽
                    sheet.SetColumnWidth(column, col.colWidth * 256);
                }
                SetColCellType(col, dt);
                var cel = headerRow1.CreateCell(column);
                cel.SetCellValue(col.header);
                cel.SetCellType(CellType.String);
                cel.CellStyle = headStyle;
                if (col.columns != null && col.columns.Count > 0)
                {
                    if (col.columns.Count > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, column, column + col.columns.Count - 1));//需-1，防止多合并一列
                    }
                    foreach (var col2 in col.columns)
                    {
                        SetColCellType(col2, dt);
                        var cel2 = headerRow2.CreateCell(column);
                        cel2.SetCellValue(col2.header);
                        cel.SetCellType(CellType.String);
                        cel.CellStyle = headStyle;
                        //第二行表头也可以设置列宽
                        if (col2.colWidth > 0)
                        {
                            //设置列宽
                            sheet.SetColumnWidth(column, col2.colWidth * 256);
                        }
                        column++;
                    }
                }
                else
                {
                    if (HeaderRowsMax == 2)//大于2改为==2，HeaderRowsMax只有1和2，防止永远不合并单元格
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 1, column, column)); // 合并单元格 
                    }
                    column++;
                }
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(i + HeaderRowsMax); // 创建数据行
                    var dic = dt.Rows[i];
                    column = 0;
                    foreach (var col in columns)
                    {

                        if (col.columns != null && col.columns.Count > 0)
                        {
                            foreach (var col2 in col.columns)
                            {
                                ICell cel = dataRow.CreateCell(column);
                                string value = dic[col2.field].ToString();

                                SetCellValue(cel, col2, value, workbook, format);
                                column++;
                            }
                        }
                        else
                        {
                            ICell cel = dataRow.CreateCell(column);
                            string value = dic[col.field].ToString();
                            if (col.CellType == NPOIExcelCellType.Png && value.IsNotNullAndEmpty())
                            {
                                string[] urls = value.Split(",");
                                int m = 0;
                                foreach (string url in urls)
                                {
                                    try
                                    {
                                        getPicture(workbook, column, m, i + 1, url, patriarch);
                                    } catch(Exception e)
                                    {

                                    }
                                    m++;
                                }
                            }
                            else
                            {
                                SetCellValue(cel, col, value, workbook, format);
                            }
                            
                            column++;
                        }
                    }
                }
            }
            string filePath = dir + name + ".xlsx";
            string directoryName = Path.GetDirectoryName(filePath);
            if ((directoryName.Length > 0) && (!Directory.Exists(directoryName)))
            {
                Directory.CreateDirectory(directoryName);
            }
            FileStream fileS = new FileStream(filePath, FileMode.Create);//保存 

            workbook.Write(fileS);
            fileS.Close();
            workbook = null;
        }
        /// <summary>  
        /// 由DataSet导出Excel  
        /// </summary>  
        /// <param name="sourceTable">要导出数据的DataTable</param>     
        /// <param name="sheetName">工作表名称</param>  
        /// <returns>Excel工作表</returns>     
        private static Stream ExportDataSetToExcel(DataSet sourceDs)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                ISheet sheet = workbook.CreateSheet(sourceDs.Tables[i].TableName);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.             
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                // handling value.             
                int rowIndex = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush();
            workbook = null;
            return ms;
        }

        /// <summary>  
        /// 由DataTable导出Excel  
        /// </summary>  
        /// <param name="sourceTable">要导出数据的DataTable</param>  
        /// <returns>Excel工作表</returns>     
        private static Stream ExportDataTableToExcel(DataTable sourceTable)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            ISheet sheet = workbook.CreateSheet(sourceTable.TableName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.       
            foreach (DataColumn column in sourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            // handling value.       
            int rowIndex = 1;
            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }

        /// <summary>  
        /// 传入行数超过65325的Table，返回DataSet  
        /// </summary>  
        /// <param name="dt"></param>  
        /// <returns></returns>  
        public static DataSet GetdtGroup(DataTable dt)
        {
            string tablename = dt.TableName;

            DataSet ds = new DataSet();
            ds.Tables.Add(dt);

            double n = dt.Rows.Count / 65325;

            //创建表  
            for (int i = 1; i < n; i++)
            {
                DataTable dtAdd = dt.Clone();
                dtAdd.TableName = tablename + "_" + i.ToString();
                ds.Tables.Add(dtAdd);
            }

            //分解数据  
            for (int i = 1; i < ds.Tables.Count; i++)
            {
                //新表行数达到最大 或 基表数量不足  
                while (ds.Tables[i].Rows.Count != 65325 && ds.Tables[0].Rows.Count != 65325)
                {
                    ds.Tables[i].Rows.Add(ds.Tables[0].Rows[65325].ItemArray);
                    ds.Tables[0].Rows.RemoveAt(65325);

                }
            }

            return ds;
        }

        /// <summary>  
        /// 由DataTable导出Excel  
        /// </summary>  
        /// <param name="sourceTable">要导出数据的DataTable</param>  
        /// <param name="fileName">指定Excel工作表名称</param>  
        /// <returns>Excel工作表</returns>  
        public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, int modelName, string fileName, string sheetName)
        {
            int rowIndex = 0;
            int colIndex = 0;
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            XSSFSheet sheet1 = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
            string[] strArry = GetColumnsByDataTable(sourceTable, modelName);
            XSSFRow xlsrow = (XSSFRow)sheet1.CreateRow(0);
            int a = 0;
            for (int i = 0; i < strArry.Length; i++)
            {
                if (!string.IsNullOrEmpty(strArry[i]))
                {
                    xlsrow.CreateCell(i - a).SetCellValue(strArry[i]);
                }
                else
                {
                    a += 1;
                }

            }
            foreach (DataRow row in sourceTable.Rows)
            {   //双循环写入sourceTable中的数据  
                rowIndex++;
                colIndex = 0;
                xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
                foreach (DataColumn col in sourceTable.Columns)
                {
                    if (col.ColumnName != "sort" && col.ColumnName != "a")
                    {
                        xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());
                        colIndex++;
                    }

                }
            }
            sheet1.ForceFormulaRecalculation = true;
            string filePath = modelpath + fileName + ".xls";
            string directoryName = Path.GetDirectoryName(filePath);
            if ((directoryName.Length > 0) && (!Directory.Exists(directoryName)))
            {
                Directory.CreateDirectory(directoryName);
            }
            FileStream fileS = new FileStream(filePath, FileMode.Create);//保存 
            int firstrow = 1;
            int lastrow = 1;
            if (modelName == 1)
            {
                for (var i = 0; i < sourceTable.Rows.Count / 4; i++)
                {
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 0, 0));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 1, 1));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 2, 2));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 3, 3));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 4, 4));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 5, 5));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 6, 6));
                    //sheet1.AddMergedRegion(new CellRangeAddress(lastrow, lastrow + 1, 4, 4));
                    //sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 2, lastrow + 3, 4, 4));
                    //sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 4, lastrow + 5, 4, 4));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow, lastrow + 1, 7, 7));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 2, lastrow + 3, 7, 7));
                    //sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 4, lastrow + 5, 7, 7));
                    firstrow += 4;
                    lastrow += 4;

                }
            } else if (modelName == 2)
            {
                int number = 0;
                for (var i = 0; i < sourceTable.Rows.Count / 4; i++)
                {
                    if (number == 1)
                    {
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 1, 0, 0));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 1, 1, 1));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 1, 2, 2));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 1, 3, 3));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 1, 4, 4));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 1, 5, 5));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 1, 6, 6));
                        sheet1.AddMergedRegion(new CellRangeAddress(lastrow, lastrow + 1, 7, 7));
                        sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 2, lastrow + 3, 7, 7));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow + 2, firstrow + 3, 0, 0));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow + 2, firstrow + 3, 1, 1));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow + 2, firstrow + 3, 2, 2));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow + 2, firstrow + 3, 3, 3));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow + 2, firstrow + 3, 4, 4));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow + 2, firstrow + 3, 5, 5));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow + 2, firstrow + 3, 6, 6));
                        number = 0;
                    }
                    else {
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 0, 0));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 1, 1));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 2, 2));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 3, 3));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 4, 4));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 5, 5));
                        sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 6, 6));
                        //sheet1.AddMergedRegion(new CellRangeAddress(lastrow, lastrow + 1, 4, 4));
                        //sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 2, lastrow + 3, 4, 4));
                        //sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 4, lastrow + 5, 4, 4));
                        sheet1.AddMergedRegion(new CellRangeAddress(lastrow, lastrow + 1, 7, 7));
                        sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 2, lastrow + 3, 7, 7));
                        //sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 4, lastrow + 5, 7, 7));
                        number += 1;
                    }

                    firstrow += 4;
                    lastrow += 4;
                    

                }
            }
            else
            {
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 7, 8));
                for (var i = 0; i < sourceTable.Rows.Count / 6; i++)
                {
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 5, 0, 0));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 5, 1, 1));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 5, 2, 2));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 5, 3, 3));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 5, 4, 4));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 5, 5, 5));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow, lastrow + 1, 6, 6));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 2, lastrow + 3, 6, 6));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 4, lastrow + 5, 6, 6));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow, lastrow + 1, 7, 7));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 2, lastrow + 3, 7, 7));
                    sheet1.AddMergedRegion(new CellRangeAddress(lastrow + 4, lastrow + 5, 7, 7));
                    firstrow += 6;
                    lastrow += 6;

                }

            }

            xssfworkbook.Write(fileS);
            fileS.Close();
        }
        public static string[] GetColumnsByDataTable(DataTable dt,int type)
        {
            string[] strColumns = null;
            if (dt.Columns.Count > 0)
            {
                int columnNum = 0;
                columnNum = dt.Columns.Count;
                strColumns = new string[columnNum];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName != "sort" && dt.Columns[i].ColumnName != "a")
                    {
                        if (dt.Columns[i].ColumnName == "dep_code")
                        {
                            if (type == 1 || type == 2)
                            {
                                dt.Columns.Remove("dep_code");
                            }
                            else
                            {
                                dt.Columns[i].ColumnName = "生产单位";
                            }
                            
                        }
                        else if (dt.Columns[i].ColumnName == "order_id")
                        {
                            dt.Columns[i].ColumnName = "订单编号";
                        }
                        else if (dt.Columns[i].ColumnName == "product_code")
                        {
                            dt.Columns[i].ColumnName = "货号";
                        }
                        else if (dt.Columns[i].ColumnName == "order_number")
                        {
                            dt.Columns[i].ColumnName = "工单数量";
                        }
                        else if (dt.Columns[i].ColumnName == "confirm_time")
                        {
                            dt.Columns[i].ColumnName = "确定交期";
                        }
                        else if (dt.Columns[i].ColumnName == "product_colour")
                        {
                            dt.Columns[i].ColumnName = "产品颜色";
                        }
                        else if (dt.Columns[i].ColumnName == "completed_number")
                        {
                            dt.Columns[i].ColumnName = "上月已完工数量";
                        }
                        else if (dt.Columns[i].ColumnName == "completion")
                        {
                            dt.Columns[i].ColumnName = "完成情况";
                        }
                        else if (dt.Columns[i].ColumnName == "day")
                        {
                            dt.Columns[i].ColumnName = "完成情况1";
                        }
                        else if (dt.Columns[i].ColumnName == "process_id")
                        {
                            dt.Columns[i].ColumnName = "总装单位";
                        }
                        else if (dt.Columns[i].ColumnName == "dep_name")
                        {
                            dt.Columns[i].ColumnName = "生产单位";
                        }
                        else if (dt.Columns[i].ColumnName == "product_number")
                        {
                            dt.Columns[i].ColumnName = "本月生产数量";
                        }
                        else if (dt.Columns[i].ColumnName == "photo_url")
                        {
                            dt.Columns[i].ColumnName = "图片";
                        }

                        strColumns[i] = dt.Columns[i].ColumnName;
                    }

                }
            }
            return strColumns;
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.ToLower().IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.ToLower().IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 将excel 2003中的数据导入到DataTable中
        /// </summary>
        /// <param name="fs">excel的stream</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(Stream fs, bool isFirstRowColumn, string fileName)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                if (fileName.ToLower().IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.ToLower().IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 将excel 2003中的数据导入到DataTable中
        /// </summary>
        /// <param name="fs">excel的stream</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable XlsToDataTable(Stream fs, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                workbook = new HSSFWorkbook(fs);
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }


        /// <summary>
        /// 将excel 2007中的数据导入到DataTable中
        /// </summary>
        /// <param name="fs">excel的stream</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable XlsxToDataTable(Stream fs, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook workbook = null;
            try
            {
                workbook = new XSSFWorkbook(fs);
                return Excel2Datatable(isFirstRowColumn, ref sheet, data, ref startRow, workbook);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        private static DataTable Excel2Datatable(bool isFirstRowColumn, ref ISheet sheet, DataTable data, ref int startRow, IWorkbook workbook)
        {
            if (workbook == null)
            {
                return null;
            }

            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }

                //最后一列的标号
                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue; //没有数据的行默认是null　　　　　　　

                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null) //同理，没有数据的单元格都默认是null
                        {
                            if (cell.CellType == CellType.Numeric)
                            {
                                //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型 
                                if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                {
                                    dataRow[j] = cell.DateCellValue;
                                }
                                else//其他数字类型
                                {
                                    dataRow[j] = cell.NumericCellValue;
                                }
                            }
                            else if (cell.CellType == CellType.Blank)//空数据类型
                            {
                                dataRow[j] = "";
                            } 
                            else if (cell.CellType == CellType.Formula)//空数据类型
                            {
                                HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                dataRow[j] = eva.Evaluate(cell).StringValue;
                            }
                            else
                            {
                                dataRow[j] = cell.StringCellValue;
                            } 
                        }
                            
                    }
                    data.Rows.Add(dataRow);
                }
            }
            return data;
        }

        public static void SetColCellType(ExcelColumn col, DataTable dt) {
            if (col.CellType == NPOIExcelCellType.Unknown && !string.IsNullOrEmpty(col.field) && dt.Columns.Contains(col.field))
            {
                var HeadColumn = dt.Columns[col.field];
                switch (HeadColumn.DataType.Name.ToString().ToLower())
                {
                    case "string":
                        col.CellType = NPOIExcelCellType.String;
                        break;
                    case "date":
                    case "datetime":
                        col.CellType = NPOIExcelCellType.DateTime; 
                        break;
                    case "boolean":
                        col.CellType = NPOIExcelCellType.Boolean;
                        break;
                    case "int64":
                    case "int32":
                    case "double":
                    case "decimal":
                        col.CellType = NPOIExcelCellType.Numeric;
                        break;
                    default:
                        col.CellType = NPOIExcelCellType.String;
                        break;
                }

            }
        }

        public static void SetCellValue(ICell cel, ExcelColumn col, string value, IWorkbook workbook, IDataFormat format) {
            if (string.IsNullOrEmpty(value))
            {
                return;
            }
            value = value.Replace("<br>", "\r\n");
            try
            {
                switch (col.CellType)
                {
                    case NPOIExcelCellType.Numeric:
                        cel.SetCellValue(CommonHelper.ToDouble(value));
                        break;
                    case NPOIExcelCellType.String:
                        cel.SetCellValue(value);
                        break;
                    case NPOIExcelCellType.Formula:
                        cel.SetCellFormula(col.Formula);
                        break; 
                    case NPOIExcelCellType.Boolean:
                        cel.SetCellValue(CommonHelper.ToBool(value));
                        break;

                    case NPOIExcelCellType.DateTime:
                        cel.SetCellValue( CommonHelper.ToDateTime(value));
                        
                        if (string.IsNullOrEmpty(col.Format) && col.CellStyle == null)
                        {
                            ICellStyle cellStyleDateDefault = workbook.CreateCellStyle();
                            cellStyleDateDefault.DataFormat = format.GetFormat("yyyy/m/d");
                            /* CellFormat cfDate = CellFormat.GetInstance("yyyy/MM/dd");*/
                            col.CellStyle = cellStyleDateDefault;
                        }
                        break;
                    case NPOIExcelCellType.Png:
                        
                        break;
                    default:
                        cel.SetCellValue(value);
                        break;
                }
                if (col.CellStyle == null && !string.IsNullOrEmpty(col.Format))
                {
                    ICellStyle celStyle = workbook.CreateCellStyle();
                    celStyle.DataFormat = format.GetFormat(col.Format);
                    /* CellFormat cfDate = CellFormat.GetInstance("yyyy/MM/dd");*/
                    col.CellStyle = celStyle;
                }
                if (col.CellStyle != null)
                {
                    cel.CellStyle = col.CellStyle;
                }
            }
            catch (Exception)
            {
                cel.SetCellValue(value);
            }
            
        }

        //根据图片url获取图片bytes
        public static byte[] UrlToByte(String filePath)
        {
            //new Uri(new Uri(filePath), relativePath).ToString();
            //第一步：读取图片到byte数组
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(filePath);

            byte[] bytes;
            using (Stream stream = request.GetResponse().GetResponseStream())
            {
                using (MemoryStream mstream = new MemoryStream())
                {
                    int count = 0;
                    byte[] buffer = new byte[1024];
                    int readNum = 0;
                    while ((readNum = stream.Read(buffer, 0, 1024)) > 0)
                    {
                        count = count + readNum;
                        mstream.Write(buffer, 0, readNum);
                    }
                    mstream.Position = 0;
                    using (BinaryReader br = new BinaryReader(mstream))
                    {
                        bytes = br.ReadBytes(count);
                    }
                }
            }
            return bytes;

        }

        public static void getPicture(XSSFWorkbook workbook, int column, int m, int i, string url, XSSFDrawing patriarch)
        {
            byte[] byteS = null;
            try
            {
                byteS = UrlToByte(SXHttpContext.Current.Request.Scheme + "://" + SXHttpContext.Current.Request.Host + url.SafeTrim());
            }
            catch (Exception e)
            {
                throw;
            }
            int pictureIdx = workbook.AddPicture(byteS, PictureType.PNG);
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 50, column + m, i, column + m + 1, i + 1);
            //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50

            XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
        }

        /// <summary>
        /// 考勤确认报表
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="modelpath"></param>
        public static void ExportDataTableToExcelConModel(DataTable dt, string modelpath, string fileName, string[] time)
        {
            int rowIndex = 3;
            int colIndex = 0;
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            XSSFSheet sheet1 = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
            sheet1.DisplayGridlines = true; //网格线

            // 样式
            ICellStyle notesStyle = xssfworkbook.CreateCellStyle();
            notesStyle.VerticalAlignment = VerticalAlignment.Center;
            notesStyle.Alignment = HorizontalAlignment.Center; //居中
            notesStyle.WrapText = true;//设置换行这个要先设置
            notesStyle.BorderBottom = BorderStyle.Thin; //边框线
            notesStyle.BorderLeft = BorderStyle.Thin;
            notesStyle.BorderRight = BorderStyle.Thin;
            notesStyle.BorderTop = BorderStyle.Thin;

            string start = time[0];
            //start=start.Substring(start.Length - 2, 2);

            // 标题样式
            ICellStyle titStyle = xssfworkbook.CreateCellStyle();
            titStyle.VerticalAlignment = VerticalAlignment.Center;
            titStyle.Alignment = HorizontalAlignment.Center;
            titStyle.WrapText = true;//设置换行这个要先设置
            IFont f = xssfworkbook.CreateFont();
            f.FontHeightInPoints = 14;
            f.IsBold = true;
			//f.Boldweight = (short)FontBoldWeight.Bold;
			titStyle.SetFont(f);


            XSSFRow title = (XSSFRow)sheet1.CreateRow(0);
            ICell tit = title.CreateCell(0);
            tit.SetCellValue("ស៊ីអិនឌឺហ្រ្គី​ អ៊ីនធើណេសិនណល(ខេអេច)ឯក\n" + "CNDEGRE INTERNATIONAL(KH) CO., LTD \n" + "Attendence " + start + " - " + time[1]);
            tit.CellStyle = titStyle;
            title.HeightInPoints = 30;


            XSSFRow xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
            foreach (DataColumn col in dt.Columns)
            {
                ICell cols = xlsrow.CreateCell(col.Ordinal);
                cols.SetCellValue(col.ColumnName);
                cols.CellStyle = notesStyle;
                if (col.Ordinal == 0)
                {
                    sheet1.SetColumnWidth(col.Ordinal, 10 * 100);
                }
                else
                {
                    sheet1.SetColumnWidth(col.Ordinal, 15 * 200);
                }

            }
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
                foreach (DataColumn col in dt.Columns)
                {
                    ICell icell = xlsrow.CreateCell(colIndex);
                    string val = row[col.ColumnName].ToString();

                    if (IsNumber(val) && col.ColumnName != "Employee #")
                    {
                        double vald = CommonHelper.ToDouble(val);
                        vald = Math.Round(vald, 2, MidpointRounding.AwayFromZero);
                        if (vald != 0)
                        {
                            icell.SetCellValue(vald);
                        }
                    }
                    else
                    {
                        icell.SetCellValue(val);
                    }
                    icell.CellStyle = notesStyle;
                    colIndex++;

                }
            }
            // 打印设置
            sheet1.PrintSetup.Landscape = true;
            sheet1.PrintSetup.PaperSize = 9;
            sheet1.FitToPage = true;
            sheet1.IsPrintGridlines = false;
            sheet1.PrintSetup.FitWidth = 1;
            sheet1.PrintSetup.FitHeight = 0;
            sheet1.HorizontallyCenter = true;
            //sheet1.VerticallyCenter = true;
            sheet1.SetMargin(NPOI.SS.UserModel.MarginType.BottomMargin, 0.1 / 3);
            sheet1.SetMargin(NPOI.SS.UserModel.MarginType.LeftMargin, 0.1 / 3);
            sheet1.SetMargin(NPOI.SS.UserModel.MarginType.RightMargin, 0.1 / 3);
            sheet1.SetMargin(NPOI.SS.UserModel.MarginType.TopMargin, 0.1 / 3);
            sheet1.SetMargin(NPOI.SS.UserModel.MarginType.FooterMargin, 0.1 / 3);
            sheet1.SetMargin(NPOI.SS.UserModel.MarginType.HeaderMargin, 0.1 / 3);

            string filePath = modelpath + fileName + ".xlsx";
            string directoryName = Path.GetDirectoryName(filePath);
            if ((directoryName.Length > 0) && (!Directory.Exists(directoryName)))
            {
                Directory.CreateDirectory(directoryName);
            }
            FileStream fileS = new FileStream(filePath, FileMode.Create);
            int firstrow = 4;
            int lastrow = 4;
            int vals = dt.Columns.Count;
            sheet1.AddMergedRegion(new CellRangeAddress(0, 2, 0, vals - 1));
            for (var i = 0; i < dt.Rows.Count / 4; i++)
            {
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 0, 0));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 1, 1));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 2, 2));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 3, 3));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 4, 4));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 5, 5));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 6, 6));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 7, 7));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, vals - 1, vals - 1));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, vals - 2, vals - 2));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, vals - 3, vals - 3));
                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, vals - 4, vals - 4));
                /*                sheet1.AddMergedRegion(new CellRangeAddress(firstrow, firstrow + 3, 6, 6))*/
                ;
                firstrow += 4;
                lastrow += 4;

            }
            xssfworkbook.Write(fileS);
            fileS.Close();
        }

        /// <summary>
            /// 用于将excel表格中列索引转成列号字母，从A对应1开始
           /// </summary>
           /// <param name="index">列索引</param>
           /// <returns>列号</returns>
        public static string IndexToColumn(int index)
        {
            if (index <= 0)
            {
                throw new Exception("Invalid parameter");
            }
            index--;
            string column = string.Empty;
            do
            {
                if (column.Length > 0)
                {
                    index--;
                }
                column = ((char)(index % 26 + (int)'A')).ToString() + column;
                index = (int)((index - index % 26) / 26);
            } while (index > 0);
            return column;
        }
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="modelpath"></param>
        public static void ExportDataTableToExcel(DataTable dt, string modelpath, string fileName)
        {
            int rowIndex = 0;
            int colIndex = 0;
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            XSSFSheet sheet1 = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
            sheet1.DisplayGridlines = true; //网格线

            // 样式
            ICellStyle notesStyle = xssfworkbook.CreateCellStyle();
            notesStyle.VerticalAlignment = VerticalAlignment.Center;
            notesStyle.Alignment = HorizontalAlignment.Center; //居中
            notesStyle.WrapText = true;//设置换行这个要先设置
            notesStyle.BorderBottom = BorderStyle.Thin; //边框线
            notesStyle.BorderLeft = BorderStyle.Thin;
            notesStyle.BorderRight = BorderStyle.Thin;
            notesStyle.BorderTop = BorderStyle.Thin;


            XSSFRow xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
            foreach (DataColumn col in dt.Columns)
            {
                ICell cols = xlsrow.CreateCell(col.Ordinal);
                cols.SetCellValue(col.ColumnName);
                cols.CellStyle = notesStyle;
                sheet1.SetColumnWidth(col.Ordinal, 15 * 250);
                xlsrow.Height = 40 * 30;

            }
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
                xlsrow.Height = 50 * 20;
                foreach (DataColumn col in dt.Columns)
                {
                    ICell icell = xlsrow.CreateCell(colIndex);
                    string val = row[col.ColumnName].ToString();

                    if (IsNumber(val) && col.ColumnName != "Employee #" && col.ColumnName != "Document Number" && col.ColumnName != "Account No.")
                    {
                        double vald = CommonHelper.ToDouble(val);
                        vald = Math.Round(vald, 2, MidpointRounding.AwayFromZero);
                        if (vald != 0)
                        {
                            icell.SetCellValue(vald);
                        }
                    }
                    else
                    {
                        icell.SetCellValue(val);
                    }
                    icell.CellStyle = notesStyle;
                    colIndex++;

                }
            }
            string filePath = modelpath + fileName + ".xlsx";
            string directoryName = Path.GetDirectoryName(filePath);
            if ((directoryName.Length > 0) && (!Directory.Exists(directoryName)))
            {
                Directory.CreateDirectory(directoryName);
            }
            FileStream fileS = new FileStream(filePath, FileMode.Create);
            xssfworkbook.Write(fileS);
            fileS.Close();
        }
        /// <summary>  
        /// 由DataTable导出Excel  
        /// </summary>  
        /// <param name="sourceTable">要导出数据的DataTable</param>  
        /// <param name="fileName">指定Excel工作表名称</param>  
        /// values  1 代表考勤报表格式   2 代表工资条导出
        /// <returns>Excel工作表</returns>  
        public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, int modelName, string fileName, string sheetName, int values = 0, string language = "")
        {
            int rowIndex = 1;
            int colIndex = 0;
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            XSSFSheet sheet1 = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
            sheet1.DisplayGridlines = true;
            Datas res = new Datas();
            string[] strArry = null;
            SDatas slip = new SDatas();
            if (values == 1)
            {
                strArry = GetColums(sourceTable, language);
                rowIndex = 0;
            }
            else if (values == 2 || values == 0)
            {
                slip = GetColumnsSlip(sourceTable);
                strArry = slip.strzh;
                rowIndex = 2;
            }

            ICellStyle notesStyle = xssfworkbook.CreateCellStyle();
            notesStyle.VerticalAlignment = VerticalAlignment.Center;
            notesStyle.Alignment = HorizontalAlignment.Center;
            if (values == 0 || values == 2)
            {
                notesStyle.WrapText = true;//设置换行这个要先设置
                notesStyle.BorderBottom = BorderStyle.Thin; //边框线
                notesStyle.BorderLeft = BorderStyle.Thin;
                notesStyle.BorderRight = BorderStyle.Thin;
                notesStyle.BorderTop = BorderStyle.Thin;
            }

            XSSFRow xlsrow = (XSSFRow)sheet1.CreateRow(0);
            xlsrow.HeightInPoints = 45;
            int a = 0;
            for (int i = 0; i < strArry.Length; i++)
            {
                if (!string.IsNullOrEmpty(strArry[i]))
                {
                    ICell icell = xlsrow.CreateCell(i - a);
                    icell.SetCellValue(strArry[i]);
                    icell.CellStyle = notesStyle;
                }
                else
                {
                    a += 1;
                }
                if (values == 1)
                {
                    if (i == 1)
                    {
                        sheet1.SetColumnWidth(i, 25 * 256);
                    }
                    else if (i == 4 || i == 5)
                    {
                        sheet1.SetColumnWidth(i, 18 * 256);
                    }
                    else
                    {
                        sheet1.SetColumnWidth(i, 13 * 256);
                    }

                }
            }
            if (values == 2 || values == 0)
            {
                xlsrow = (XSSFRow)sheet1.CreateRow(1);
                xlsrow.HeightInPoints = 70;
                strArry = slip.stren;
                a = 0;
                for (int i = 0; i < strArry.Length; i++)
                {
                    if (!string.IsNullOrEmpty(strArry[i]))
                    {
                        ICell icell = xlsrow.CreateCell(i - a);
                        icell.SetCellValue(strArry[i]);
                        icell.CellStyle = notesStyle;
                    }
                    else
                    {
                        a += 1;
                    }
                }
                xlsrow = (XSSFRow)sheet1.CreateRow(2);
                xlsrow.HeightInPoints = 70;
                strArry = slip.strkm;
                a = 0;
                for (int i = 0; i < strArry.Length; i++)
                {
                    if (!string.IsNullOrEmpty(strArry[i]))
                    {
                        ICell icell = xlsrow.CreateCell(i - a);
                        icell.SetCellValue(strArry[i]);
                        icell.CellStyle = notesStyle;
                    }
                    else
                    {
                        a += 1;
                    }
                }
            }
            if (values == 2)
            {
                foreach (DataRow row in sourceTable.Rows)
                {   //双循环写入sourceTable中的数据  
                    if (rowIndex != 2)
                    {
                        rowIndex = rowIndex + 2;
                        for (var j = 0; j < 3; j++)
                        {
                            rowIndex += 1;
                            xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
                            xlsrow.HeightInPoints = 70;
                            if (j == 0)
                            {
                                strArry = slip.strzh;
                                xlsrow.HeightInPoints = 45;
                            }
                            else if (j == 1)
                            {
                                strArry = slip.stren;
                            }
                            else if (j == 2)
                            {
                                strArry = slip.strkm;
                            }

                            a = 0;
                            for (int i = 0; i < strArry.Length; i++)
                            {
                                if (!string.IsNullOrEmpty(strArry[i]))
                                {
                                    ICell icell = xlsrow.CreateCell(i - a);
                                    icell.SetCellValue(strArry[i]);
                                    icell.CellStyle = notesStyle;
                                }
                                else
                                {
                                    a += 1;
                                }
                            }
                        }

                    }
                    rowIndex += 1;
                    colIndex = 0;
                    xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
                    foreach (DataColumn col in sourceTable.Columns)
                    {
                        ICell icell = xlsrow.CreateCell(colIndex);
                        string val = row[col.ColumnName].ToString();
                        if (IsNumber(val) && col.ColumnName != "staff_code")
                        {

                            double vals = CommonHelper.ToDouble(val);
                            vals = Math.Round(vals, 2, MidpointRounding.AwayFromZero);
                            if (vals != 0)
                            {
                                icell.SetCellValue(vals);
                            }
                        }
                        else
                        {
                            icell.SetCellValue(val);
                        }
                        icell.CellStyle = notesStyle;
                        colIndex++;
                    }
                }
            }
            else
            {
                foreach (DataRow row in sourceTable.Rows)
                {   //双循环写入sourceTable中的数据  
                    rowIndex++;
                    colIndex = 0;
                    xlsrow = (XSSFRow)sheet1.CreateRow(rowIndex);
                    foreach (DataColumn col in sourceTable.Columns)
                    {
                        if (col.ColumnName == "am_w_m" || col.ColumnName == "pm_w_m" || col.ColumnName == "day_w_h")
                        {
                            continue;
                        }
                        ICell icell = xlsrow.CreateCell(colIndex);
                        string val = row[col.ColumnName].ToString();
                        if (values == 1)
                        {
                            string worktype = row["work_type"].ToString();
                            string att_date_type = row["att_date_type"].ToString();
                            int am_w_m = CommonHelper.ToInt(row["am_w_m"].ToString());
                            int pm_w_m = CommonHelper.ToInt(row["pm_w_m"].ToString());
                            int day_w_h = CommonHelper.ToInt(row["day_w_h"].ToString(), 8);
                            if (val.IsNullOrEmpty() && (am_w_m + pm_w_m) < (day_w_h * 60) && (col.ColumnName == "dt_am_fr" || col.ColumnName == "dt_am_to" || col.ColumnName == "dt_pm_fr" || col.ColumnName == "dt_pm_to"))
                            {
                                switch (worktype)
                                {
                                    case "1":
                                        val = language == "zh" ? "无记录" : "No Record";
                                        break;
                                    case "0":
                                        val = "";
                                        break;
                                    default:
                                        val = language == "zh" ? row["name_zh"].ToString() : row["name_en"].ToString();
                                        break;
                                }
                                if (val.IsNullOrEmpty())
                                {
                                    switch (att_date_type)
                                    {
                                        case "0":
                                            val = language == "zh" ? "周日" : "Sunday";
                                            break;
                                        case "2":
                                            val = language == "zh" ? "公休" : "Holiday";
                                            break;
                                        default:
                                            val = "";
                                            break;
                                    }
                                }
                            }
                        }
                        if (IsNumber(val) && col.ColumnName != "staff_code" && val.IsNotNullOrEmpty())
                        {
                            double vals = CommonHelper.ToDouble(val);
                            vals = Math.Round(vals, 2, MidpointRounding.AwayFromZero);
                            if (col.ColumnName != "work_type" && col.ColumnName != "name_zh" && col.ColumnName != "name_en" && col.ColumnName != "att_date_type")
                            {
                                if (vals != 0)
                                {
                                    icell.SetCellValue(vals);
                                }
                                icell.CellStyle = notesStyle;
                                colIndex++;
                            }
                        }
                        else
                        {
                            if (col.ColumnName != "work_type" && col.ColumnName != "name_zh" && col.ColumnName != "name_en" && col.ColumnName != "att_date_type")
                            {
                                icell.SetCellValue(val);
                                icell.CellStyle = notesStyle;
                                colIndex++;
                            }
                        }
                    }
                }
            }
            sheet1.ForceFormulaRecalculation = true;
            if (values == 2)
            {
                sheet1.PrintSetup.Landscape = true;
                sheet1.PrintSetup.PaperSize = 9;
                sheet1.FitToPage = true;
                sheet1.IsPrintGridlines = false;
                sheet1.PrintSetup.FitWidth = 1;
                sheet1.PrintSetup.FitHeight = 0;
                sheet1.HorizontallyCenter = true;
                sheet1.SetMargin(NPOI.SS.UserModel.MarginType.BottomMargin, 0.1 / 3);
                sheet1.SetMargin(NPOI.SS.UserModel.MarginType.LeftMargin, 0.1 / 3);
                sheet1.SetMargin(NPOI.SS.UserModel.MarginType.RightMargin, 0.1 / 3);
                sheet1.SetMargin(NPOI.SS.UserModel.MarginType.TopMargin, 0.1 / 3);
                sheet1.SetMargin(NPOI.SS.UserModel.MarginType.FooterMargin, 0.1 / 3);
                sheet1.SetMargin(NPOI.SS.UserModel.MarginType.HeaderMargin, 0.1 / 3);
            }
            if (values == 0)
            {
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 2, 4));
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 7, 9));
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 19, 21));
            }
            else if (values == 2)
            {
                int firstrow = 0;
                int lastrow = 0;
                int vals = sourceTable.Columns.Count;
                for (var i = 0; i < sourceTable.Rows.Count; i++)
                {

                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, 2, 4));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, 7, 9));
                    sheet1.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, 19, 21));
                    firstrow += 6;
                    lastrow += 6;

                }
            }
            string filePath = modelpath + fileName + ".xlsx";
            string directoryName = Path.GetDirectoryName(filePath);
            if ((directoryName.Length > 0) && (!Directory.Exists(directoryName)))
            {
                Directory.CreateDirectory(directoryName);
            }
            FileStream fileS = new FileStream(filePath, FileMode.Create);//保存 

            xssfworkbook.Write(fileS);
            fileS.Close();
        }
        public class Datas
        {
            public string[] strcou { get; set; }
            public string[] str { get; set; }
        }
        public static Datas GetColumnsByDataTable(DataTable dt)
        {
            string[] strColumns = null;
            string[] str = null;
            List<string> strList = new List<string>();
            List<string> strCList = new List<string>();
            List<string> strKList = new List<string>();
            if (dt.Columns.Count > 0)
            {
                int columnNum = 0;
                columnNum = dt.Columns.Count;
                strColumns = new string[columnNum];
                str = new string[columnNum];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName == "rowid")
                    {
                        //dt.Columns[i].ColumnName = "序号";
                        strCList.Add("序号");
                        strList.Add("No.");
                    }
                    else if (dt.Columns[i].ColumnName == "staff_code")
                    {
                        //dt.Columns[i].ColumnName = "工号";
                        strCList.Add("工号");
                        strList.Add("I.D NO.");
                        //dt.Columns[i+1].ColumnName = "I.D NO.";
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_en")
                    {
                        //dt.Columns[i].ColumnName = "姓名";
                        strCList.Add("姓名");
                        strList.Add("NAME");
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_km")
                    {
                        //dt.Columns[i].ColumnName = "姓名1";
                        strCList.Add("姓名");
                        strList.Add("NAME");
                    }
                    else if (dt.Columns[i].ColumnName == "dep_name")
                    {
                        //dt.Columns[i].ColumnName = "部门";
                        strCList.Add("部门");
                        strList.Add("DEPARTMENT");
                    }
                    else if (dt.Columns[i].ColumnName == "pos_name")
                    {
                        //dt.Columns[i].ColumnName = "职位";
                        strCList.Add("职位");
                        strList.Add("POSITION");
                    }
                    else if (dt.Columns[i].ColumnName == "base_salary")
                    {
                        //dt.Columns[i].ColumnName = "底薪";
                        strCList.Add("底薪");
                        strList.Add("Basic Salary Computation");
                    }
                    else if (dt.Columns[i].ColumnName == "sincentive_allowance")
                    {
                        //dt.Columns[i].ColumnName = "浮动绩效";
                        strCList.Add("浮动绩效");
                        strList.Add("Incentives");
                    }
                    else if (dt.Columns[i].ColumnName == "tran_hous")
                    {
                        //dt.Columns[i].ColumnName = "车费/宿舍补贴";
                        strCList.Add("车费/宿舍补贴");
                        strList.Add("Transportation /House Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "att_bonus")
                    {
                        //dt.Columns[i].ColumnName = "全勤奖";
                        strCList.Add("全勤奖");
                        strList.Add("Perfect Attendance Award");
                    }
                    else if (dt.Columns[i].ColumnName == "sfood_fee")
                    {
                        //dt.Columns[i].ColumnName = "餐费";
                        strCList.Add("餐费");
                        strList.Add("Food Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "sot_food_fee")
                    {
                        //dt.Columns[i].ColumnName = "加班餐补";
                        strCList.Add("加班餐补");
                        strList.Add("Food Allowance if with O.T / per Hr ");
                    }
                    else if (dt.Columns[i].ColumnName == "sch_allowance")
                    {
                        //dt.Columns[i].ColumnName = "中文津贴";
                        strCList.Add("中文津贴");
                        strList.Add("Chinese speaking allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_days")
                    {
                        //dt.Columns[i].ColumnName = "薪酬周期加班天数";
                        strCList.Add("薪酬周期加班天数");
                        strList.Add("w/o.t allowance (# of days)");
                    }
                    else if (dt.Columns[i].ColumnName == "h_status")
                    {
                        //dt.Columns[i].ColumnName = "是否享受加班工资";
                        strCList.Add("是否享受加班工资");
                        strList.Add("O.T STATUS PAYMENT");
                    }
                    else if (dt.Columns[i].ColumnName == "child")
                    {
                        //dt.Columns[i].ColumnName = "家庭状况/孩子";
                        strCList.Add("家庭状况/孩子");
                        strList.Add("familysituation/child");
                    }
                    else if (dt.Columns[i].ColumnName == "act_w_d")
                    {
                        //dt.Columns[i].ColumnName = "当月实际出勤天数";
                        strCList.Add("当月实际出勤天数");
                        strList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "half_fee_days")
                    {
                        //dt.Columns[i].ColumnName = "50%半薪+50%回归奖天数";
                        strCList.Add("50%半薪+50%回归奖天数");
                        strList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "hours")
                    {
                        //dt.Columns[i].ColumnName = "算薪时数";
                        strCList.Add("算薪时数");
                        strList.Add("actual Attendance hours");
                    }
                    else if (dt.Columns[i].ColumnName == "need_w_d")
                    {
                        //dt.Columns[i].ColumnName = "天数";
                        strCList.Add("天数");
                        strList.Add("# of Days");
                    }
                    else if (dt.Columns[i].ColumnName == "wage")
                    {
                        //dt.Columns[i].ColumnName = "出勤工资";
                        strCList.Add("出勤工资");
                        strList.Add("Attendance wage");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_hour")
                    {
                        //dt.Columns[i].ColumnName = "加班时数";
                        strCList.Add("加班时数");
                        strList.Add("O.T HRS");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_pay")
                    {
                        //dt.Columns[i].ColumnName = "加班费";
                        strCList.Add("加班费");
                        strList.Add("O.T PAY");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_ph_count")
                    {
                        //dt.Columns[i].ColumnName = "假日加班次数";
                        strCList.Add("假日加班次数");
                        strList.Add("Holiday  Work");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_ph_m")
                    {
                        //dt.Columns[i].ColumnName = "假日加班时数";
                        strCList.Add("假日加班时数");
                        strList.Add("Holiday  Work Hours");
                    }
                    else if (dt.Columns[i].ColumnName == "holiday_ov_pay")
                    {
                        //dt.Columns[i].ColumnName = "假日加班工资";
                        strCList.Add("假日加班工资");
                        strList.Add("Holiday Pay");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_sun_count")
                    {
                        //dt.Columns[i].ColumnName = "周日加班";
                        strCList.Add("周日加班");
                        strList.Add("Sunday Work");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_sun_m")
                    {
                        //dt.Columns[i].ColumnName = "周日加班小时数";
                        strCList.Add("周日加班小时数");
                        strList.Add("Sunday work hours");
                    }
                    else if (dt.Columns[i].ColumnName == "sun_pay")
                    {
                        //dt.Columns[i].ColumnName = "周日加班费";
                        strCList.Add("周日加班费");
                        strList.Add("Sunday Pay");
                    }
                    else if (dt.Columns[i].ColumnName == "ch_allowance")
                    {
                        //dt.Columns[i].ColumnName = "说中文补贴";
                        strCList.Add("说中文补贴");
                        strList.Add("Chinese speaking Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "tech_allowance")
                    {
                        //dt.Columns[i].ColumnName = "技术补贴";
                        strCList.Add("技术补贴");
                        strList.Add("Technical Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "pos_allowance")
                    {
                        //dt.Columns[i].ColumnName = "职位津贴";
                        strCList.Add("职位津贴");
                        strList.Add("Position Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "ext_att_bonus")
                    {
                        //dt.Columns[i].ColumnName = "全勤奖";
                        strCList.Add("全勤奖");
                        strList.Add("Perfect Attendance Award");
                    }
                    else if (dt.Columns[i].ColumnName == "tran_fee")
                    {
                        //dt.Columns[i].ColumnName = "车费";
                        strCList.Add("车费");
                        strList.Add("Transportation");
                    }
                    else if (dt.Columns[i].ColumnName == "housing_fee")
                    {
                        //dt.Columns[i].ColumnName = "宿舍补贴";
                        strCList.Add("宿舍补贴");
                        strList.Add("Housing Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "food_fee")
                    {
                        //dt.Columns[i].ColumnName = "餐费";
                        strCList.Add("餐费");
                        strList.Add("Food Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "phone_fee")
                    {
                        //dt.Columns[i].ColumnName = "话补";
                        strCList.Add("话补");
                        strList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_food_fee")
                    {
                        //dt.Columns[i].ColumnName = "加班餐补";
                        strCList.Add("加班餐补");
                        strList.Add("Additional allowance (w/ o.t food allowance)");
                    }
                    else if (dt.Columns[i].ColumnName == "incentive_allowance")
                    {
                        //dt.Columns[i].ColumnName = "浮动绩效";
                        strCList.Add("浮动绩效");
                        strList.Add("Incentive Allowance");
                    }
                    else if (dt.Columns[i].ColumnName == "annual_leave_amount")
                    {
                        //dt.Columns[i].ColumnName = "年假金额";
                        strCList.Add("年假金额");
                        strList.Add("annual leave Amount");
                    }
                    else if (dt.Columns[i].ColumnName == "other_fee")
                    {
                        //dt.Columns[i].ColumnName = "补发工资";
                        strCList.Add("补发工资");
                        strList.Add("Compensation");
                    }
                    else if (dt.Columns[i].ColumnName == "loan_fee")
                    {
                        //dt.Columns[i].ColumnName = "借支";
                        strCList.Add("借支");
                        strList.Add("Advance");
                    }
                    else if (dt.Columns[i].ColumnName == "gross_salary")
                    {
                        //dt.Columns[i].ColumnName = "应发工资";
                        strCList.Add("应发工资");
                        strList.Add("Gross Salary ");
                    }
                    else if (dt.Columns[i].ColumnName == "tax_fee")
                    {
                        //dt.Columns[i].ColumnName = "扣稅";
                        strCList.Add("扣稅");
                        strList.Add("Tax deduction");
                    }
                    else if (dt.Columns[i].ColumnName == "union_fee")
                    {
                        //dt.Columns[i].ColumnName = "扣工會";
                        strCList.Add("扣工會");
                        strList.Add("Deduction union");
                    }
                    else if (dt.Columns[i].ColumnName == "total_amount")
                    {
                        //dt.Columns[i].ColumnName = "实发金额";
                        strCList.Add("实发金额");
                        strList.Add("Total amount(Net Salary)");
                    }
                    else if (dt.Columns[i].ColumnName == "signs")
                    {
                        //dt.Columns[i].ColumnName = "";
                        strCList.Add("签名");
                        strList.Add("Signature");
                    }
                    else
                    {
                        strCList.Add(" ");
                        strList.Add(" ");
                    }

                    //strColumns[i] = dt.Columns[i].ColumnName;
                }
                strColumns = strCList.ToArray();
                str = strList.ToArray();

            }
            var result = new Datas();
            result.strcou = strColumns;
            result.str = str;
            return result;
        }


        public class SDatas
        {
            public string[] strzh { get; set; }
            public string[] stren { get; set; }
            public string[] strkm { get; set; }
        }
        public static SDatas GetColumnsSlip(DataTable dt)
        {
            string[] strColumns = null;
            string[] str = null;
            string[] strkm = null;
            List<string> strList = new List<string>();
            List<string> strCList = new List<string>();
            List<string> strKMList = new List<string>();
            if (dt.Columns.Count > 0)
            {
                int columnNum = 0;
                columnNum = dt.Columns.Count;
                strColumns = new string[columnNum];
                str = new string[columnNum];
                strkm = new string[columnNum];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName == "rowid")
                    {
                        //dt.Columns[i].ColumnName = "序号";
                        strCList.Add("序号");
                        strList.Add("No.");
                        strKMList.Add("ល.រ");
                    }
                    else if (dt.Columns[i].ColumnName == "staff_code")
                    {
                        //dt.Columns[i].ColumnName = "工号";
                        strCList.Add("工号");
                        strList.Add("I.D NO.");
                        strKMList.Add("អត្ថលេខ");
                        //dt.Columns[i+1].ColumnName = "I.D NO.";
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_en")
                    {
                        //dt.Columns[i].ColumnName = "姓名";
                        strCList.Add("姓名");
                        strList.Add("NAME");
                        strKMList.Add("ឈ្មោះ");
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_km")
                    {
                        //dt.Columns[i].ColumnName = "姓名1";
                        strCList.Add("姓名");
                        strList.Add("NAME");
                        strKMList.Add("ឈ្មោះ");
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_zh")
                    {
                        //dt.Columns[i].ColumnName = "姓名1";
                        strCList.Add("姓名");
                        strList.Add("NAME");
                        strKMList.Add("ឈ្មោះ");
                    }
                    else if (dt.Columns[i].ColumnName == "dep_name")
                    {
                        //dt.Columns[i].ColumnName = "部门";
                        strCList.Add("部门");
                        strList.Add("DEPARTMENT");
                        strKMList.Add("ផ្នែក");
                    }
                    else if (dt.Columns[i].ColumnName == "pos_name")
                    {
                        //dt.Columns[i].ColumnName = "职位";
                        strCList.Add("职位");
                        strList.Add("POSITION");
                        strKMList.Add("តួនាទី");
                    }
                    else if (dt.Columns[i].ColumnName == "base_salary1")
                    {
                        //dt.Columns[i].ColumnName = "底薪";
                        strCList.Add("当月核算底薪");
                        strList.Add("Basic Before");
                        strKMList.Add("ប្រាក់ ខែគោល 185$");
                    }
                    else if (dt.Columns[i].ColumnName == "base_salary2")
                    {
                        //dt.Columns[i].ColumnName = "底薪";
                        strCList.Add(" ");
                        strList.Add("Basic After");
                        strKMList.Add("190$");
                    }
                    else if (dt.Columns[i].ColumnName == "base_salary")
                    {
                        //dt.Columns[i].ColumnName = "底薪";
                        strCList.Add(" ");
                        strList.Add("Basic");
                        strKMList.Add("លុយធ្វើការ");
                    }
                    else if (dt.Columns[i].ColumnName == "sincentive_allowance")
                    {
                        //dt.Columns[i].ColumnName = "浮动绩效";
                        strCList.Add("浮动绩效");
                        strList.Add("Incentives");
                        strKMList.Add("ប្រាក់រង្វាន់តួនាទី");
                    }
                    else if (dt.Columns[i].ColumnName == "tran_hous")
                    {
                        //dt.Columns[i].ColumnName = "车费/宿舍补贴";
                        strCList.Add("车费/宿舍补贴");
                        strList.Add("Transportation /House Allowance");
                        strKMList.Add("លុយធ្វើដំណើរ");
                    }
                    else if (dt.Columns[i].ColumnName == "att_bonus")
                    {
                        //dt.Columns[i].ColumnName = "全勤奖";
                        strCList.Add("全勤奖");
                        strList.Add("Perfect Attendance Award");
                        strKMList.Add("លុយទៀងទាត់");
                    }
                    else if (dt.Columns[i].ColumnName == "sfood_fee")
                    {
                        //dt.Columns[i].ColumnName = "餐费";
                        strCList.Add("餐费");
                        strList.Add("Food Allowance");
                        strKMList.Add("លុយថ្លៃបាយ");
                    }
                    else if (dt.Columns[i].ColumnName == "sot_food_fee")
                    {
                        //dt.Columns[i].ColumnName = "加班餐补";
                        strCList.Add("加班餐补");
                        strList.Add("Food Allowance if with O.T / per Hr ");
                        strKMList.Add("លុយថ្លៃបាយ ថែមម៉ោង");
                    }
                    else if (dt.Columns[i].ColumnName == "sch_allowance")
                    {
                        //dt.Columns[i].ColumnName = "中文津贴";
                        strCList.Add("中文津贴");
                        strList.Add("Chinese speaking allowance");
                        strKMList.Add("និយាយភាសាចិន");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_days")
                    {
                        //dt.Columns[i].ColumnName = "薪酬周期加班天数";
                        strCList.Add("薪酬周期加班天数");
                        strList.Add("w/o.t allowance (# of days)");
                        strKMList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "h_status")
                    {
                        //dt.Columns[i].ColumnName = "是否享受加班工资";
                        strCList.Add("是否享受加班工资");
                        strList.Add("O.T STATUS PAYMENT");
                        strKMList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "child")
                    {
                        //dt.Columns[i].ColumnName = "家庭状况/孩子";
                        strCList.Add("家庭状况/孩子");
                        strList.Add("familysituation/child");
                        strKMList.Add("គ្រួសារ/កូន");
                    }
                    else if (dt.Columns[i].ColumnName == "w_m1")
                    {
                        //dt.Columns[i].ColumnName = "当月实际出勤天数";
                        strCList.Add("当月实际出勤天数");
                        strList.Add("Days of 185");
                        strKMList.Add("ចំនួនថ្ងៃ185");
                    }
                    else if (dt.Columns[i].ColumnName == "w_m2")
                    {
                        //dt.Columns[i].ColumnName = "当月实际出勤天数";
                        strCList.Add(" ");
                        strList.Add("Days of 190");
                        strKMList.Add("ចំនួនថ្ងៃ190");
                    }
                    else if (dt.Columns[i].ColumnName == "act_w_d")
                    {
                        //dt.Columns[i].ColumnName = "当月实际出勤天数";
                        strCList.Add(" ");
                        strList.Add("# of Days");
                        strKMList.Add("ចំនួនថ្ងៃធ្វើការ");
                    }
                    else if (dt.Columns[i].ColumnName == "half_fee_days")
                    {
                        //dt.Columns[i].ColumnName = "50%半薪+50%回归奖天数";
                        strCList.Add("50%半薪+50%回归奖天数");
                        strList.Add("50%");
                        strKMList.Add("50%");
                    }
                    else if (dt.Columns[i].ColumnName == "hours")
                    {
                        //dt.Columns[i].ColumnName = "算薪时数";
                        strCList.Add("算薪时数");
                        strList.Add("actual Attendance hours");
                        strKMList.Add("ចំនួនម៉ាងធ្វើការសរុប");
                    }
                    else if (dt.Columns[i].ColumnName == "need_w_d")
                    {
                        //dt.Columns[i].ColumnName = "天数";
                        strCList.Add("天数");
                        strList.Add("# of Days");
                        strKMList.Add("ចំនួនថ្ងៃធ្វើកា");
                    }
                    else if (dt.Columns[i].ColumnName == "wage")
                    {
                        //dt.Columns[i].ColumnName = "出勤工资";
                        strCList.Add("出勤工资");
                        strList.Add("Attendance wage");
                        strKMList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_hour")
                    {
                        //dt.Columns[i].ColumnName = "加班时数";
                        strCList.Add("加班时数");
                        strList.Add("O.T HRS");
                        strKMList.Add("បន្ថែម ម៉ោង");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_pay")
                    {
                        //dt.Columns[i].ColumnName = "加班费";
                        strCList.Add("加班费");
                        strList.Add("O.T PAY");
                        strKMList.Add("ប្រាក់បន្ថែមម៉ោង");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_ph_count")
                    {
                        //dt.Columns[i].ColumnName = "假日加班次数";
                        strCList.Add("假日加班次数");
                        strList.Add("Holiday  Work");
                        strKMList.Add("ចំនួនថ្ងៃបន្ថែមម៉ោងថ្ងៃបុណ្យ");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_ph_m")
                    {
                        //dt.Columns[i].ColumnName = "假日加班时数";
                        strCList.Add("假日加班时数");
                        strList.Add("Holiday  Work Hours");
                        strKMList.Add("ចំនួនម៉ោងបន្ថែមថ្ងៃបុណ្យ");
                    }
                    else if (dt.Columns[i].ColumnName == "holiday_ov_pay")
                    {
                        //dt.Columns[i].ColumnName = "假日加班工资";
                        strCList.Add("假日加班工资");
                        strList.Add("Holiday Pay");
                        strKMList.Add("ប្រាក់បន្ថែមម៉ោងថ្ងៃបុណ្យ​200%");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_sun_count")
                    {
                        //dt.Columns[i].ColumnName = "周日加班";
                        strCList.Add("周日加班");
                        strList.Add("Sunday Work");
                        strKMList.Add("ចំនួនថ្ងៃបន្ថែមម៉ោងថ្ងៃអាទិត្យ");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_sun_m")
                    {
                        //dt.Columns[i].ColumnName = "周日加班小时数";
                        strCList.Add("周日加班小时数");
                        strList.Add("Sunday work hours");
                        strKMList.Add("ចំនួនម៉ោងបន្ថែមម៉ោងថ្ងៃអាទិត្យ");
                    }
                    else if (dt.Columns[i].ColumnName == "sun_pay")
                    {
                        //dt.Columns[i].ColumnName = "周日加班费";
                        strCList.Add("周日加班费");
                        strList.Add("Sunday Pay");
                        strKMList.Add("ប្រាក់បន្ថែមម៉ោងថ្ងៃអាទិត្យ​200%");
                    }
                    else if (dt.Columns[i].ColumnName == "ch_allowance")
                    {
                        //dt.Columns[i].ColumnName = "说中文补贴";
                        strCList.Add("说中文补贴");
                        strList.Add("Chinese speaking Allowance");
                        strKMList.Add("និយាយភាសាចិន");
                    }
                    else if (dt.Columns[i].ColumnName == "tech_allowance")
                    {
                        //dt.Columns[i].ColumnName = "技术补贴";
                        strCList.Add("技术补贴");
                        strList.Add("Technical Allowance");
                        strKMList.Add("ប្រាក់ជំនាញ");
                    }
                    else if (dt.Columns[i].ColumnName == "pos_allowance")
                    {
                        //dt.Columns[i].ColumnName = "职位津贴";
                        strCList.Add("职位津贴");
                        strList.Add("Position Allowance");
                        strKMList.Add("រង្វាន់ មុខតំណែង");
                    }
                    else if (dt.Columns[i].ColumnName == "ext_att_bonus")
                    {
                        //dt.Columns[i].ColumnName = "全勤奖";
                        strCList.Add("全勤奖");
                        strList.Add("Perfect Attendance Award");
                        strKMList.Add("លុយទៀងទាត់");
                    }
                    else if (dt.Columns[i].ColumnName == "tran_fee")
                    {
                        //dt.Columns[i].ColumnName = "车费";
                        strCList.Add("车费");
                        strList.Add("Transportation");
                        strKMList.Add("លុយធ្វើដំណើរ");
                    }
                    else if (dt.Columns[i].ColumnName == "housing_fee")
                    {
                        //dt.Columns[i].ColumnName = "宿舍补贴";
                        strCList.Add("宿舍补贴");
                        strList.Add("Housing Allowance");
                        strKMList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "food_fee")
                    {
                        //dt.Columns[i].ColumnName = "餐费";
                        strCList.Add("餐费");
                        strList.Add("Food Allowance");
                        strKMList.Add("លុយថ្លៃបាយ");
                    }
                    else if (dt.Columns[i].ColumnName == "phone_fee")
                    {
                        //dt.Columns[i].ColumnName = "话补";
                        strCList.Add("话补");
                        strList.Add("Telephone Subsidy");
                        strKMList.Add(" ");
                    }
                    else if (dt.Columns[i].ColumnName == "ot_food_fee")
                    {
                        //dt.Columns[i].ColumnName = "加班餐补";
                        strCList.Add("加班餐补");
                        strList.Add("Additional allowance (w/ o.t food allowance)");
                        strKMList.Add("លុយថ្លៃបាយ ថែមម៉ោង");
                    }
                    else if (dt.Columns[i].ColumnName == "incentive_allowance")
                    {
                        //dt.Columns[i].ColumnName = "浮动绩效";
                        strCList.Add("浮动绩效");
                        strList.Add("Incentive Allowance");
                        strKMList.Add("ប្រាក់រង្វាន់តួនាទី");
                    }
                    else if (dt.Columns[i].ColumnName == "annual_leave_amount")
                    {
                        //dt.Columns[i].ColumnName = "年假金额";
                        strCList.Add("年假金额");
                        strList.Add("annual leave Amount");
                        strKMList.Add("ប្រាក់បំណាច់ឆ្នាំ");
                    }
                    else if (dt.Columns[i].ColumnName == "other_fee")
                    {
                        //dt.Columns[i].ColumnName = "补发工资";
                        strCList.Add("补发工资");
                        strList.Add("Compensation");
                        strKMList.Add("លុយប៉ូវ");
                    }
                    else if (dt.Columns[i].ColumnName == "per_5_fee")
                    {
                        //dt.Columns[i].ColumnName = "补发工资";
                        strCList.Add("5%奖金");
                        strList.Add("5% Reward");
                        strKMList.Add("៥% ភាគរយ");
                    }
                    else if (dt.Columns[i].ColumnName == "loan_fee")
                    {
                        //dt.Columns[i].ColumnName = "借支";
                        strCList.Add("借支");
                        strList.Add("Advance");
                        strKMList.Add("ប្រាក់កម្ចី");
                    }
                    else if (dt.Columns[i].ColumnName == "gross_salary")
                    {
                        //dt.Columns[i].ColumnName = "应发工资";
                        strCList.Add("应发工资");
                        strList.Add("Gross Salary ");
                        strKMList.Add("ប្រាក់សរុប");
                    }
                    else if (dt.Columns[i].ColumnName == "tax_fee")
                    {
                        //dt.Columns[i].ColumnName = "扣稅";
                        strCList.Add("扣稅");
                        strList.Add("Tax deduction");
                        strKMList.Add("កាត់ពន្ឋ");
                    }
                    else if (dt.Columns[i].ColumnName == "union_fee")
                    {
                        //dt.Columns[i].ColumnName = "扣工會";
                        strCList.Add("扣工會");
                        strList.Add("Deduction union");
                        strKMList.Add("កាត់សហជិព");
                    }
                    else if (dt.Columns[i].ColumnName == "total_amount")
                    {
                        //dt.Columns[i].ColumnName = "实发金额";
                        strCList.Add("实发金额");
                        strList.Add("Total amount(Net Salary)");
                        strKMList.Add("ប្រាក់សរុបដែលត្រូវបើកចុងក្រោយ");
                    }
                    else if (dt.Columns[i].ColumnName == "signs")
                    {
                        //dt.Columns[i].ColumnName = " ";
                        strCList.Add("签名");
                        strList.Add("Signature");
                        strKMList.Add("ពី ១ ដល់​១៥ ហត្ថលេខា");
                    }
                    else
                    {
                        strCList.Add(" ");
                        strList.Add(" ");
                        strKMList.Add(" ");
                    }

                    //strColumns[i] = dt.Columns[i].ColumnName;
                }
                strColumns = strCList.ToArray();
                str = strList.ToArray();
                strkm = strKMList.ToArray();

            }
            var result = new SDatas();
            result.strzh = strColumns;
            result.stren = str;
            result.strkm = strkm;
            return result;
        }
        public static bool IsNumber(string input)
        {
            string pattern = "^-?\\d+$|^(-?\\d+)(\\.\\d+)?$";
            Regex regex = new Regex(pattern);
            return regex.IsMatch(input);
        }
        public static string[] GetColums(DataTable dt, string language)
        {
            string[] strColumns = null;
            List<string> strList = new List<string>();
            if (dt.Columns.Count > 0)
            {
                int columnNum = 0;
                columnNum = dt.Columns.Count;
                strColumns = new string[columnNum];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName == "att_date")
                    {
                        if (language == "zh")
                        {
                            strList.Add("日期");
                        }
                        else
                        {
                            strList.Add("Date");
                        }

                    }
                    else if (dt.Columns[i].ColumnName == "dep_name")
                    {
                        if (language == "zh")
                        {
                            strList.Add("部门(课、班组)");
                        }
                        else
                        {
                            strList.Add("Departments(Classes, Teams)");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "pos_name")
                    {
                        if (language == "zh")
                        {
                            strList.Add("岗位");
                        }
                        else
                        {
                            strList.Add("Post");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "EnrollNumber")
                    {
                        if (language == "zh")
                        {
                            strList.Add("员工编码");
                        }
                        else
                        {
                            strList.Add("Employee #");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_zh")
                    {
                        if (language == "zh")
                        {
                            strList.Add("中文姓名");
                        }
                        else
                        {
                            strList.Add("CN Name");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_en")
                    {
                        if (language == "zh")
                        {
                            strList.Add("英文姓名");
                        }
                        else
                        {
                            strList.Add("EN Name");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "staff_name_km")
                    {
                        if (language == "zh")
                        {
                            strList.Add("柬文姓名");
                        }
                        else
                        {
                            strList.Add("KH Name");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "work_h")
                    {
                        if (language == "zh")
                        {
                            strList.Add("正常上班时间");
                        }
                        else
                        {
                            strList.Add("Normal working hours");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "ot_hour")
                    {
                        if (language == "zh")
                        {
                            strList.Add("加班时间");
                        }
                        else
                        {
                            strList.Add("OT");
                        }
                    }
                    else if (dt.Columns[i].ColumnName == "dt_am_fr")
                    {
                        strList.Add("Time In");
                    }
                    else if (dt.Columns[i].ColumnName == "dt_am_to")
                    {
                        strList.Add("Time Out");
                    }
                    else if (dt.Columns[i].ColumnName == "dt_pm_fr")
                    {
                        strList.Add("Time In");
                    }
                    else if (dt.Columns[i].ColumnName == "dt_pm_to")
                    {
                        strList.Add("Time Out");
                    }
                }
                strColumns = strList.ToArray();

            }
            return strColumns;
        }


        ///// <summary>
        ///// 图片在单元格等比缩放居中显示
        ///// </summary>
        ///// <param name="cell">单元格</param>
        ///// <param name="value">图片二进制流</param>
        //private static void CellImage(ICell cell, byte[] value)
        //{
        //    if (value.Length == 0) return;
        //    //空图片处理
        //    double scalx = 0;  //x轴缩放比例
        //    double scaly = 0; //y轴缩放比例
        //    int Dx1 = 0; //图片左边相对excel格的位置(x偏移) 范围值为:0~1023,超过1023就到右侧相邻的单元格里了
        //    int Dy1 = 0; //图片上方相对excel格的位置(y偏移) 范围值为:0~256,超过256就到下方的单元格里了
        //    bool bOriginalSize = false; //是否显示图片原始大小 true表示图片显示原始大小  false表示显示图片缩放后的大小
        //    ///计算单元格的长度和宽度
        //    double CellWidth = 0;
        //    double CellHeight = 0;
        //    int RowSpanCount = cell.GetSpan().RowSpan;//合并的单元格行数
        //    int ColSpanCount = cell.GetSpan().ColSpan;
        //    //合并的单元格列数
        //    int j = 0; for (j = 0; j < RowSpanCount; j++) //根据合并的行数计算出高度
        //    {
        //        CellHeight += cell.Sheet.GetRow(cell.RowIndex + j).Height;
        //    }
        //    for (j = 0; j < ColSpanCount; j++) { CellWidth += cell.Row.Sheet.GetColumnWidth(cell.ColumnIndex + j); }//单元格长度和宽度与图片的长宽单位互换是根据实例得出
        //    CellWidth = CellWidth / 35; CellHeight = CellHeight / 15;
        //    ///计算图片的长度和宽度
        //    MemoryStream ms = new MemoryStream(value);
        //    System.Drawing.Image Img = System.Drawing.Bitmap.FromStream(ms, true);
        //    double ImageOriginalWidth = Img.Width;//原始图片的长度
        //    double ImageOriginalHeight = Img.Height;//原始图片的宽度
        //    double ImageScalWidth = 0;//缩放后显示在单元格上的图片长度
        //    double ImageScalHeight = 0;//缩放后显示在单元格上的图片宽度
        //    if (CellWidth > ImageOriginalWidth && CellHeight > ImageOriginalHeight)//单元格的长度和宽度比图片的大，说明单元格能放下整张图片，不缩放
        //    { ImageScalWidth = ImageOriginalWidth; ImageScalHeight = ImageOriginalHeight; bOriginalSize = true; }
        //    else//需要缩放，根据单元格和图片的长宽计算缩放比例
        //    {
        //        bOriginalSize = false; if (ImageOriginalWidth > CellWidth && ImageOriginalHeight > CellHeight)//图片的长和宽都比单元格的大的情况
        //        {
        //            double WidthSub = ImageOriginalWidth - CellWidth;//图片长与单元格长的差距
        //            double HeightSub = ImageOriginalHeight - CellHeight;//图片宽与单元格宽的差距
        //            if (WidthSub > HeightSub)//长的差距比宽的差距大时,长度x轴的缩放比为1，表示长度就用单元格的长度大小，宽度y轴的缩放比例需要根据x轴的比例来计算
        //            {
        //                scalx = 1; scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight;//计算y轴的缩放比例,CellWidth / ImageWidth计算出图片整体的缩放比例,然后 * ImageHeight计算出单元格应该显示的图片高度,然后/ CellHeight就是高度的缩放比例
        //            }
        //            else { scaly = 1; scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth; }
        //        }
        //        else if (ImageOriginalWidth > CellWidth && ImageOriginalHeight < CellHeight)//图片长度大于单元格长度但图片高度小于单元格高度，此时长度不需要缩放，直接取单元格的，因此scalx=1，但图片高度需要等比缩放
        //        { scalx = 1; scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight; }
        //        else if (ImageOriginalWidth < CellWidth && ImageOriginalHeight > CellHeight)//图片长度小于单元格长度但图片高度大于单元格高度，此时单元格高度直接取单元格的，scaly = 1,长度需要等比缩放
        //        { scaly = 1; scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth; }
        //        ImageScalWidth = scalx * CellWidth; ImageScalHeight = scaly * CellHeight;
        //    }
        //    Dx1 = Convert.ToInt32((CellWidth - ImageScalWidth) / CellWidth * 1023 / 2);
        //    Dy1 = Convert.ToInt32((CellHeight - ImageScalHeight) / CellHeight * 256 / 2);
        //    int pictureIdx = cell.Sheet.Workbook.AddPicture((Byte[])value, PictureType.PNG);
        //    IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
        //    anchor.AnchorType = AnchorType.MoveDontResize;
        //    anchor.Col1 = cell.ColumnIndex; 
        //    anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColSpan; 
        //    anchor.Row1 = cell.RowIndex;
        //    anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan;
        //    anchor.Dy1 = Dy1;//图片下移量
        //    anchor.Dx1 = Dx1;//图片右移量，通过图片下移和右移，使得图片能居中显示，因为图片不同文字，图片是浮在单元格上的，文字是钳在单元格里的
        //    IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch(); 
        //    IPicture pic = patriarch.CreatePicture(anchor, pictureIdx);
        //    if (bOriginalSize)
        //    {
        //        pic.Resize();//显示图片原始大小 
        //    }
        //    else
        //    {
        //        pic.Resize(scalx, scaly);//等比缩放   
        //    }
        //}
    }

    public class ExcelColumn
    {
        public ExcelColumn(){
            this.CellType = NPOIExcelCellType.Unknown;
            this.HaveMultiHeaders = 0;
            this.colWidth = 0;
        }
        public string header { get; set; }
        public List<string> headers { get; set; }
        public string field { get; set; }
        public NPOIExcelCellType CellType { get; set; }
        /// <summary>
        /// 计算公式
        /// </summary>
        public string Formula { get; set; }

        /// <summary>
        /// 输出格式
        /// </summary>
        public string Format { get; set; }
        public List<ExcelColumn> columns { get; set; }

        public ICellStyle CellStyle { get; set; }
        public int HaveMultiHeaders { get; set; }
        /// <summary>
        /// Excel列宽
        /// </summary>
        public int colWidth { get; set; }
    }


    
    public enum NPOIExcelCellType {
        Unknown = -1,
        Numeric = 0,
        String = 1,
        Formula = 2,
        Blank = 3,
        Boolean = 4,
        
        Error = 5,
        DateTime = 6,
        // 序号
        Index = 9,
        Image = 10,
        Png = 11
    }

}
